﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading;

namespace MovieBase
{
    class SqlHandle
    {
        #region ATTRIBUTE
        /// <summary>
        /// DataSet für interne Datenverarbeitung
        /// </summary>
        private DataSet m_Data = null;

        /// <summary>
        /// Datatable für interne Datenverarbeitung
        /// </summary>
        private DataTable m_DBTable = null;

        string m_SourceTable;
        string m_DataSetTable;

        /// <summary>
        /// Aktueller Query
        /// </summary>     
        private string m_Query = null;

        /// <summary>
        /// Gefundene Einträge
        /// </summary>
        private int m_Matches = 0;

        /// <summary>
        /// Datenbank
        /// </summary>
        private string m_DBSource = "db.sdf";

        /// <summary>
        /// Connection zu der Datenbank
        /// </summary>
        private SQLiteConnection m_DBConnection = null;

        /// <summary>
        /// Mainthread
        /// </summary>
        private Thread mainThread = null;


        #endregion ATTRIBUTE

        #region PROERTIES
        /// <summary>
        /// Get/Set Dataset m_Data
        /// </summary>
        public DataSet DATA
        {
            get { return m_Data; }
            set { m_Data = value; }
        }

        /// <summary>
        /// Get/Set DataTable m_DBTable
        /// </summary>
        public DataTable DATATABLE
        {
            get { return m_DBTable; }
            set { m_DBTable = value; }
        }

        /// <summary>
        /// Get/Set Query
        /// </summary>
        public string QUERY
        {
            get { return m_Query; }
            set { m_Query = value; }
        }

        /// <summary>
        /// Get Matches found
        /// </summary>
        public int MATCHES
        {
            get { return m_Matches; }
        }

        /// <summary>
        /// Get/Set Fullpath to Database
        /// </summary>
        public string SOURCE
        {
            get { return m_DBSource; }
            set { m_DBSource = value; }
        }

        /// <summary>
        /// Get Status of Mainthread
        /// </summary>
        public string STATUS
        {
            get { return mainThread.ThreadState.ToString(); }
        }

        #endregion PROPERTIES

        #region KONSTRUKTOR
        public SqlHandle()
        {
            try
            {
                connect(m_DBSource);
            }
            catch (SQLiteException ex)
            {
                throw ex;
            }
        }

        public SqlHandle(string source)
        {
            m_DBSource = source;

            try
            {
                connect(m_DBSource);
            }
            catch (SQLiteException ex)
            {
                throw ex;
            }
        }

        #endregion KONSTRUKTOR

        #region THREAD

        private void startSelectThread()
        {

           // Execute(m_Query, sourceTable, dataSetTable);
        }

        private void startExecThread()
        {

            Execute(m_Query);
        }
        #endregion THREAD

        #region MEMBER

        public void Select(string query, string sourceTable, string dataSetTable)
        {
            m_Query = query;

            m_SourceTable = sourceTable;
            m_DataSetTable = dataSetTable;



            mainThread = new Thread(new ThreadStart(startSelectThread));
            mainThread.Start();

        }

        public void Exec(string query)
        {
            m_Query = query;


            mainThread = new Thread(new ThreadStart(startExecThread));
            mainThread.Start();

        }

        private void connect(string source)
        {
            if (m_DBConnection != null)
                m_DBConnection = null;

            try
            {
                m_DBConnection = new SQLiteConnection();
                m_DBConnection.ConnectionString = "Data Source=" + source;
                m_DBConnection.Open();
            }
            catch (SQLiteException Ex)
            {
                int test = 0;
            }
        }

        [STAThread]
        private void Execute(string query, string sourceTable, string dataSetTable)
        {
            try
            {
                SQLiteCommand Command = new SQLiteCommand();
                Command.Connection = m_DBConnection;
                SQLiteCommand Query = new SQLiteCommand();
                Query.Connection = m_DBConnection;
                Query.CommandText = query;
                Query.ExecuteNonQuery();
                SQLiteDataAdapter Adapter = new SQLiteDataAdapter(Query);
                Adapter.TableMappings.Add(sourceTable, dataSetTable);

                if (m_Data == null)
                    m_Data = new DataSet();
                Adapter.Fill(m_Data);
                // Freigabe der Ressourcen.
                Query.Dispose();
            }
            catch (SQLiteException ex)
            {
                throw ex;
            }
            finally
            {
                m_DBConnection.Close();
            }
        }

        [STAThread]
        private void Execute(string query)
        {
            try
            {
                SQLiteCommand Command = new SQLiteCommand();
                Command.Connection = m_DBConnection;
                SQLiteCommand Query = new SQLiteCommand();
                Query.Connection = m_DBConnection;
                Query.CommandText = query;
                Query.ExecuteNonQuery();
                SQLiteDataAdapter Adapter = new SQLiteDataAdapter(Query);

                // Freigabe der Ressourcen.
                Query.Dispose();
            }
            catch (SQLiteException ex)
            {
                throw ex;
            }
            finally
            {
                m_DBConnection.Close();
            }
        }

        #endregion MEMBER
    }

}
